import pandas as pd
import time
import warnings
import os
import math
warnings.simplefilter('ignore')

class Scalp:
    # 指定文件路径
    paths = "C:/ExcelData"
    filepath = str(time.strftime("%Y{y}%m{m}%d{d}%H{h}%M{f}%S{s}", time.localtime())).format(y='年', m='月',
                                                                                             d='日', h='时',
                                                                                             f='分', s='秒')
    balepath = "C:/ExcelData/包号模板"
    put_arrivepath = "C:/ExcelData/收,到件模板"
    deliverypath = "C:/ExcelData/派件模板"
    unusedbalepath = "C:/ExcelData/未使用"
    oddbalepath  = "C:/ExcelData/装包模板"
    initpath = "C:/ExcelData/初始模板"

    # 创建文件目录,如有同名目录不创建,没有同名目录就创建目录
    def Creating_file_Directory(self):
        if not os.path.exists(self.balepath):
            os.makedirs(self.balepath)
        if not os.path.exists(self.put_arrivepath):
            os.makedirs(self.put_arrivepath)
        if not os.path.exists(self.deliverypath):
            os.makedirs(self.deliverypath)
        if not os.path.exists(self.unusedbalepath):
            os.makedirs(self.unusedbalepath)
        if not os.path.exists(self.oddbalepath):
            os.makedirs(self.oddbalepath)
        if not os.path.exists(self.initpath):
            os.makedirs(self.initpath)

    # 生成包号模板
    def read_balenum_exceltotemplate(self, filespath, cut):
        oldbalenum = pd.read_excel(filespath, dtype={'包号': str}).rename(columns={'包号': '单号'})
        newbalenum = pd.DataFrame(oldbalenum['单号'])
        newbalenum_cut = newbalenum.loc[0: cut-1]
        newbalenum_cut.insert(1, '重量', 5)
        newbalenum_cut.to_excel(self.balepath + "/包号模板" + self.filepath + ".xlsx", index=False)

    # 生成单号 收件模板 到件模板 派件模板
    def read_put_arrive_delivery_exceltotemplate(self, filespath, cut):
        oldodd = pd.read_excel(filespath, dtype={'单号': str})
        newodd = pd.DataFrame(oldodd['单号'])
        newodd_cut = newodd.loc[0: cut-1]
        newodd_cut = pd.DataFrame(newodd_cut)
        newodd_cut.insert(1, '重量', '')
        a = 1
        b = 0
        for i in range(newodd_cut.shape[0]):
            if (i+1) % 5000 == 0:
                newodd_cut_delivery = newodd_cut.loc[b: i]
                newodd_cut_put_arrive = newodd_cut.loc[b: i]
                newodd_cut_put_arrive['重量'] = 0.01
                newodd_cut_put_arrive.to_excel(self.put_arrivepath +
                                             "/收到件模板-" + str(a) + "___" + self.filepath + ".xlsx",
                                             index=False)
                newodd_cut_delivery.to_excel(self.deliverypath +
                                           "/派件模板-" + str(a) + "___" + self.filepath + ".xlsx",
                                           index=False)

                a += 1
                b += 5000

    # 计算截取位置 并将未使用的单号包号重新生成新的excel
    def cut_notscan_nums(self, balepath, oddpath):
        cut_nums = []
        bale_df = pd.read_excel(balepath, dtype={'包号': str})
        odd_df = pd.read_excel(oddpath, dtype={'单号': str})
        bale_df = pd.DataFrame(bale_df)
        odd_df = pd.DataFrame(odd_df)
        bale_nums = bale_df.shape[0]
        odd_nums = odd_df.shape[0]
        upodd_nums = math.floor(odd_nums / 5000) * 5000
        upbale_nums = math.floor(bale_nums * 0.1) *10
        if upbale_nums * 500 > upodd_nums:
            bale_cutnums = upodd_nums / 500
            odd_cutnums = upodd_nums
            cut_nums.append(bale_cutnums)
            cut_nums.append(odd_cutnums)
            unusedbale = bale_df.loc[bale_cutnums: bale_nums-1]
            unusedodd = odd_df.loc[odd_cutnums: odd_nums-1]
            unusedbale = pd.DataFrame(unusedbale)
            unusedodd = pd.DataFrame(unusedodd)
            unusedbale.to_excel(self.unusedbalepath +
                                "/未使用的包号" + self.filepath + ".xlsx",
                                index=False)
            unusedodd.to_excel(self.unusedbalepath +
                               "/未使用的单号" + self.filepath + ".xlsx",
                               index=False)
            usedbale = bale_df.loc[0: bale_cutnums - 1]
            usedodd = odd_df.loc[0: odd_cutnums - 1]
            usedbale = pd.DataFrame(usedbale)
            usedodd = pd.DataFrame(usedodd)
            self.odd_matching_bale(usedodd, usedbale)
            return cut_nums
        elif upbale_nums*500 < upodd_nums:
            bale_cutnums = upbale_nums
            odd_cutnums = upbale_nums*500
            cut_nums.append(bale_cutnums)
            cut_nums.append(odd_cutnums)
            unusedbale = bale_df.loc[bale_cutnums: bale_nums-1]
            unusedodd = odd_df.loc[odd_cutnums: odd_nums-1]
            unusedbale = pd.DataFrame(unusedbale)
            unusedodd = pd.DataFrame(unusedodd)
            unusedbale.to_excel(self.unusedbalepath +
                                "/未使用的包号" + self.filepath + ".xlsx",
                                index=False)
            unusedodd.to_excel(self.unusedbalepath +
                               "/未使用的单号" + self.filepath + ".xlsx",
                               index=False)
            usedbale = bale_df.loc[0: bale_cutnums - 1]
            usedodd = odd_df.loc[0: odd_cutnums - 1]
            usedbale = pd.DataFrame(usedbale)
            usedodd = pd.DataFrame(usedodd)
            self.odd_matching_bale(usedodd, usedbale)
            return cut_nums
        elif upbale_nums*500 == upodd_nums:
            if bale_nums * 500 == odd_nums:
                cut_nums.append(bale_nums)
                cut_nums.append(odd_nums)
                usedbale = bale_df.loc[0: bale_nums - 1]
                usedodd = odd_df.loc[0: odd_nums - 1]
                self.odd_matching_bale(usedodd, usedbale)
                return cut_nums
            elif bale_nums*500 > odd_nums:
                bale_cutnums = math.floor(odd_nums / 500)
                odd_cutnums = bale_cutnums * 500
                cut_nums.append(bale_cutnums)
                cut_nums.append(odd_cutnums)
                unusedbale = bale_df.loc[bale_cutnums: bale_nums - 1]
                unusedodd = odd_df.loc[odd_cutnums: odd_nums - 1]
                unusedbale = pd.DataFrame(unusedbale)
                unusedodd = pd.DataFrame(unusedodd)
                unusedbale.to_excel(self.unusedbalepath +
                                    "/未使用的包号" + self.filepath + ".xlsx",
                                    index=False)
                unusedodd.to_excel(self.unusedbalepath +
                                   "/未使用的单号" + self.filepath + ".xlsx",
                                   index=False)
                usedbale = bale_df.loc[0: bale_cutnums - 1]
                usedodd = odd_df.loc[0: odd_cutnums - 1]
                usedbale = pd.DataFrame(usedbale)
                usedodd = pd.DataFrame(usedodd)
                self.odd_matching_bale(usedodd, usedbale)
                return cut_nums
            elif bale_nums * 500 < odd_nums:
                bale_cutnums = bale_nums
                odd_cutnums = bale_nums * 500
                cut_nums.append(bale_cutnums)
                cut_nums.append(odd_cutnums)
                unusedbale = bale_df.loc[bale_cutnums: bale_nums - 1]
                unusedodd = odd_df.loc[odd_cutnums: odd_nums - 1]
                unusedbale = pd.DataFrame(unusedbale)
                unusedodd = pd.DataFrame(unusedodd)
                unusedbale.to_excel(self.unusedbalepath +
                                    "/未使用的包号" + self.filepath + ".xlsx",
                                    index=False)
                unusedodd.to_excel(self.unusedbalepath +
                                   "/未使用的单号" + self.filepath + ".xlsx",
                                   index=False)
                usedbale = bale_df.loc[0: bale_cutnums - 1]
                usedodd = odd_df.loc[0: odd_cutnums - 1]
                usedbale = pd.DataFrame(usedbale)
                usedodd = pd.DataFrame(usedodd)
                self.odd_matching_bale(usedodd, usedbale)
                return cut_nums

    # 生成装包模板
    def odd_matching_bale(self, odd, bale):
        odd_df = pd.DataFrame(odd['单号'])
        bale_df = pd.DataFrame(bale['包号'])
        odd_df.insert(1, '包号', '')
        for i in range(bale_df.shape[0]):
            for j in range(500):
                rows = 500*i+j
                odd_df.iloc[rows, 1] = bale_df.iloc[i, 0]
        a = 1
        b = 0
        for i in range(odd_df.shape[0]):
            if (i + 1) % 5000 == 0:
                newodd_cut = odd_df.loc[b: i]
                newodd_cut = pd.DataFrame(newodd_cut)
                newodd_cut.to_excel(self.oddbalepath +
                                    "/装包模板-" + str(a) + "___" + self.filepath + ".xlsx",
                                    index=False)
                a += 1
                b += 5000

    def operation_scalp(self, balepath, oddpath):
        try:
            # 创建文件目录
            self.Creating_file_Directory()
            cuts = self.cut_notscan_nums(balepath, oddpath)
            self.read_balenum_exceltotemplate(balepath, cuts[0])
            self.read_put_arrive_delivery_exceltotemplate(oddpath, cuts[1])
            return True
        except Exception:
            return False

    def open_filed(self):
        try:
            self.Creating_file_Directory()
            os.startfile(self.paths)
            return True
        except Exception:
            return False

    def create_odd_bale_template(self):
        self.Creating_file_Directory()
        data0 = {'单号': []}
        data1 = {'包号': []}
        data0_df = pd.DataFrame(data0)
        data1_df = pd.DataFrame(data1)
        data0_paths = self.initpath + \
                      "/单号初始模板" + "___" + self.filepath + ".xlsx"
        data1_paths = self.initpath +\
                      "/包号初始模板" + "___" + self.filepath + ".xlsx"
        data0_df.to_excel(data0_paths, index=False)
        data1_df.to_excel(data1_paths, index=False)
        os.startfile(data0_paths)
        os.startfile(data1_paths)